package model

import (
	"bytes"
	"errors"
	"github.com/jmoiron/sqlx"
	"strings"
)

// 人员信息
type Users struct {
	UID        int    `db:"U_ID"`        //人员标识Id
	UNumber    string `db:"U_Number"`    //人员编号
	UName      string `db:"U_Name"`      //人员姓名
	UType      string `db:"U_Type"`      //人员业务类型
	UPhone     string `db:"U_Phone"`     //人员联系电话
	ULoginName string `db:"U_LoginName"` //人员登录系统名称
	UPwd       string `db:"U_Pwd"`       //人员登录系统密码
	UAddress   string `db:"U_Address"`   //人员地址
	URemark    string `db:"U_Remark"`    //人员备注
	UStatus    string `db:"U_Status"`    //人员状态（是否有效）
	URoleId    int    `db:"U_RoleId"`    //工人角色Id
	UExt1      string `db:"U_Ext1"`      //人员信息扩展1
	UExt2      string `db:"U_Ext2"`      //人员信息扩展2
	UExt3      string `db:"U_Ext3"`      //人员信息扩展3
}

// 根据【人员标识Id】查询【人员信息】表中是否存在相关记录
func (d *Users) Exist(U_ID int) (bool, error) {
	rows, err := amp.Queryx("SELECT COUNT(0) Count FROM users WHERE U_ID=?", U_ID)
	if err != nil {
		return false, err
	}
	defer rows.Close()
	count := 0
	if rows.Next() {
		err = rows.Scan(&count)
		if err != nil {
			return false, err
		}
		return count > 0, nil
	}
	return false, nil
}

// 插入单条记录到【人员信息】表中
func (d *Users) Insert() (int64, error) {
	result, err := amp.Exec("INSERT INTO users(U_Number,U_Name,U_Type,U_Phone,U_LoginName,U_Pwd,U_Address,U_Remark,U_Status,U_RoleId,U_Ext1,U_Ext2,U_Ext3) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", d.UNumber, d.UName, d.UType, d.UPhone, d.ULoginName, d.UPwd, d.UAddress, d.URemark, d.UStatus, d.URoleId, d.UExt1, d.UExt2, d.UExt3)
	if err != nil {
		return -1, err
	}
	return result.LastInsertId()
}

// 根据【人员标识Id】修改【人员信息】表的单条记录
func (d *Users) Update() (bool, error) {
	result, err := amp.Exec("UPDATE users SET U_Number=?, U_Name=?, U_Type=?, U_Phone=?, U_LoginName=?, U_Pwd=?, U_Address=?, U_Remark=?, U_Status=?, U_RoleId=?, U_Ext1=?, U_Ext2=?, U_Ext3=? WHERE U_ID=?", d.UNumber, d.UName, d.UType, d.UPhone, d.ULoginName, d.UPwd, d.UAddress, d.URemark, d.UStatus, d.URoleId, d.UExt1, d.UExt2, d.UExt3, d.UID)
	if err != nil {
		return false, err
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return false, err
	}
	return affected > 0, nil
}

// 根据【人员标识Id】查询【人员信息】表中的单条记录
func (d *Users) Get(U_ID int) (users Users, err error) {
	rows, err := amp.Queryx("SELECT U_ID, U_Number, U_Name, U_Type, U_Phone, U_LoginName, U_Pwd, U_Address, U_Remark, U_Status, U_RoleId, U_Ext1, U_Ext2, U_Ext3 FROM users WHERE U_ID=?", U_ID)
	if err != nil {
		return users, err
	}
	defer rows.Close()
	userss, err := d._RowsToArray(rows)
	if err != nil {
		return users, err
	}
	if len(userss) <= 0 {
		return users, err
	}
	return userss[0], nil
}

// 根据【人员标识Id】数组查询【人员信息】表中的多条记录
func (d *Users) GetIn(U_IDs []int) (userss []Users, err error) {
	if len(U_IDs) <= 0 {
		return userss, errors.New("U_IDs is empty")
	}
	sqlStr := bytes.Buffer{}
	sqlStr.WriteString("SELECT U_ID, U_Number, U_Name, U_Type, U_Phone, U_LoginName, U_Pwd, U_Address, U_Remark, U_Status, U_RoleId, U_Ext1, U_Ext2, U_Ext3 FROM ")
	sqlStr.WriteString("users")
	sqlStr.WriteString(" WHERE U_ID IN(")
	paramKeys := strings.Repeat("?,", len(U_IDs))
	sqlStr.WriteString(paramKeys[:len(paramKeys)-1])
	sqlStr.WriteString(")")
	var rows *sqlx.Rows
	vals := make([]interface{}, 0, len(U_IDs))
	for _, v := range U_IDs {
		vals = append(vals, v)
	}
	rows, err = amp.Queryx(sqlStr.String(), vals...)
	if err != nil {
		return userss, err
	}
	defer rows.Close()
	return d._RowsToArray(rows)
}

// 查询【人员信息】表总记录数
func (d *Users) GetRowCount() (count int, err error) {
	rows, err := amp.Queryx("SELECT COUNT(0) Count FROM users")
	if err != nil {
		return -1, err
	}
	defer rows.Close()
	if rows.Next() {
		err = rows.Scan(&count)
		if err != nil {
			return -1, err
		}
		return count, nil
	}
	return -1, nil
}

// 分页查询【人员信息】表的记录
func (d *Users) GetRowList(PageIndex, PageSize int) (userss []Users, err error) {
	rows, err := amp.Queryx("SELECT U_ID, U_Number, U_Name, U_Type, U_Phone, U_LoginName, U_Pwd, U_Address, U_Remark, U_Status, U_RoleId, U_Ext1, U_Ext2, U_Ext3 FROM users LIMIT ?,?", (PageIndex-1)*PageSize, PageSize)
	if err != nil {
		return userss, err
	}
	defer rows.Close()
	return d._RowsToArray(rows)
}

// 解析【人员信息】表记录
func (d *Users) _RowsToArray(rows *sqlx.Rows) (models []Users, err error) {
	for rows.Next() {
		mo := Users{}
		err = rows.Scan(&mo.UID, &mo.UNumber, &mo.UName, &mo.UType, &mo.UPhone, &mo.ULoginName, &mo.UPwd, &mo.UAddress, &mo.URemark, &mo.UStatus, &mo.URoleId, &mo.UExt1, &mo.UExt2, &mo.UExt3)
		if err != nil {
			return models, err
		}
		models = append(models, mo)
	}
	return models, err
}
